package com.wuseguang.springcloud.dao.repository;

import com.wuseguang.springcloud.entity.StudentFacesRegistration;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

public interface StudentFacesRegistrationRepository extends JpaRepository<StudentFacesRegistration,Integer> {

    /**
     * 查询学校已绑定班牌的学生班级注册情况
     * @param student_faces_id
     * @param integer
     * @return
     */
    @Query(value = "select c.id class_id,c.`name` class_name,if(sfr.id is null,0,sfr.state) state from classes c " +
            "left join classroom cr on (cr.classid=c.id) left join bancard b on (cr.id=b.classroomid)   " +
            "left join student_faces_registration sfr on (sfr.deviceid = b.deviceid and sfr.student_faces_id = ?1 ) " +
            "where c.school_id = ?2 and cr.id is not null and b.id is not null  ",nativeQuery = true)
    List<Map<String,Object>> findBystudentFacesId(String student_faces_id, Integer integer);


    /**
     * 学校已绑定班牌的学生注册成功班级总数
     * @param s
     * @param valueOf
     * @return
     */
    @Query(value = "select COUNT(1) `count` from classes c " +
            "left join classroom cr on (cr.classid=c.id) left join bancard b on (cr.id=b.classroomid)   " +
            "left join student_faces_registration sfr on (sfr.deviceid = b.deviceid and sfr.student_faces_id = ?1 ) " +
            "where c.school_id = ?2 and cr.id is not null and b.id is not null and (state = 2 or state = 3 ) ",nativeQuery = true)
    Integer findCountStudentFaces(String s, Integer valueOf);

    @Transactional
    @Modifying
    @Query(value = "DELETE FROM `student_faces_registration` WHERE `student_faces_id` = ?1",nativeQuery = true)
    Integer deleteByStudentFacesId(Integer id);

    /**
     * 学校是否有设备
     * @param s
     * @return
     */
    @Query(value = "select COUNT(1) `count` from  bancard b  where b.school_id = ?1   ",nativeQuery = true)
    Integer findCountBancard(String s);



}
